package yarar.tietokanta;

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import yarar.rikai.ConfigParser;
import yarar.rikai.Logger;

/**
 * <b>Tietokanta</b> is a <a href="http://en.wikipedia.org/wiki/Finnish_language"
 * target="_blank">Finnish</a> word for <i>database</i>. <br>
 * This is a general class that manages DB-related operations.
 * 
 * @author Dimo Vanchev
 * 
 */
public final class Tietokanta {

    /**
     * Parser of DB configurations.
     */
    private final ConfigParser cf;

    /**
     * A connection to the database
     */
    private Connection db;
    /**
     * This is basically info the driver delivers about the DB it just connected to. I use it to get
     * the DB version to confirm the connection in this example.
     */
    private DatabaseMetaData dbmd;

    /**
     * Default constructor. Creates new Tietokanta object and connects to the database.
     */
    public Tietokanta() {
	// loading properties from the the relevant properties file.
	cf = new ConfigParser("tietokanta.properties");

	try {
	    connect();
	} catch (final Exception e) {
	    Logger.print(e);
	}
    }

    /**
     * Create a {@link Statement} - an object used for executing a static SQL statement and
     * returning the results it produces.
     * 
     * By default, only one <code>ResultSet</code> object per <code>Statement</code> object can be
     * open at the same time. Therefore, if the reading of one <code>ResultSet</code> object is
     * interleaved with the reading of another, each must have been generated by different
     * <code>Statement</code> objects. All execution methods in the <code>Statement</code> interface
     * implicitly close a statment's current <code>ResultSet</code> object if an open one exists.
     * 
     * @return a new default <code>Statement</code> object
     * @exception SQLException
     *                if a database access error occurs or this method is called on a closed
     *                connection
     */
    protected Statement getNewStatement() throws SQLException {
	return db.createStatement();
    }

    /**
     * Prepares a <code>Kysely</code>'s SQL. To execute the prepared query, use
     * {@link #doPreparedQuery(Kysely, TypeValuePair[])}. There is a special check to prevent
     * preparing a statement for the same Kysely more than once. If such calls are made, these get
     * silently ignored.
     * 
     * @param kysely
     *            The <code>Kysely</code> object whose SQL needs to be prepared.
     * @see Kysely
     */
    public void prepareQuery(final Kysely kysely) {
	if (kysely.hasPreparedStatement()) {
	    /* Prevents preparing the same statement twice. */
	    return;
	}
	try {
	    final PreparedStatement preparedStatement = db.prepareStatement(kysely.getQuery());
	    kysely.setPreparedStatement(preparedStatement);
	} catch (final SQLException e) {
	    Logger.print(e);
	}
    }

    /**
     * Executes a <code>Kysely</code>'s prepared query SQL. To do manipulations with the result, use
     * <code>Kysely</code>'s built-in methods.
     * 
     * @param kysely
     *            The <code>Kysely</code> object whose prepared query needs to be executed.
     * @param values
     *            Array of field type and value pairs, needed for substitution of the relevant
     *            <code>?</code> from the SQL query.
     * @see #prepareQuery(Kysely)
     */
    public void doPreparedQuery(final Kysely kysely, final TypeValuePair[] values) {
	final PreparedStatement ps = kysely.getPreparedStatement();
	try {
	    int parameterIndex = 0;
	    for (final TypeValuePair tvp : values) {
		parameterIndex++;
		final Object x = tvp.getValue();
		switch (tvp.getType()) {
		case INTEGER:
		    ps.setInt(parameterIndex, (Integer) x);
		    break;
		case STRING:
		    ps.setString(parameterIndex, (String) x);
		    break;
		case OBJECT:
		default:
		    ps.setObject(parameterIndex, x);
		    break;
		}
	    }
	    ps.execute();
	    kysely.setResult(ps.getResultSet());
	} catch (final SQLException e) {
	    Logger.print(e);
	}
    }

    /**
     * Executes a <code>Kysely</code>'s SQL. To do manipulations with the result, use
     * <code>Kysely</code>'s built-in methods.
     * 
     * @param kysely
     *            The <code>Kysely</code> object whose SQL needs to be executed.
     * @see Kysely
     */
    public void doQuery(final Kysely kysely) {
	final ResultSet result = doQuery(kysely.getQuery());
	kysely.setResult(result);
    }

    /**
     * Calls {@link #executeStatement(String)} with the specified parameter and logs any
     * {@link SQLException}.
     * 
     * @param sql
     *            A {@link String}, containing the SQL query.
     * @return The {@link ResultSet} of statement's execution, or <code>null</code> if an
     *         {@link SQLException} was caught.
     */
    private ResultSet doQuery(final String sql) {
	ResultSet rs = null;
	try {
	    rs = executeStatement(sql);
	} catch (final SQLException e) {
	    Logger.print(e);
	}

	return rs;
    }

    /**
     * Calls {@link #executeStatement(String, Statement)} with the passed <code>sql</code> parameter
     * and newly created {@link Statement} object.
     * 
     * @param sql
     *            The SQL that needs to be executed.
     * @return The {@link ResultSet} of sql's execution.
     * @throws SQLException
     *             thrown by query's execution.
     */
    protected ResultSet executeStatement(final String sql) throws SQLException {
	return executeStatement(sql, getNewStatement());
    }

    /**
     * Executes the passed sql query by the passed {@link Statement} object.
     * 
     * @param sql
     *            The SQL statement to be sent to the database, typically a static SQL
     *            <code>SELECT</code> statement
     * @param statement
     *            The object used for executing the static SQL statement and returning the results
     *            it produces.
     * @return a <code>ResultSet</code> object that contains the data produced by the given query;
     *         never <code>null</code>
     * @exception SQLException
     *                if a database access error occurs, this method is called on a closed
     *                <code>Statement</code>, the given SQL statement produces anything other than a
     *                single <code>ResultSet</code> object, the method is called on a
     *                <code>PreparedStatement</code> or <code>CallableStatement</code>
     */
    protected ResultSet executeStatement(final String sql,
	    final Statement statement) throws SQLException {
	return statement.executeQuery(sql);
    }

    /**
     * Connects to the database, using configuration read from "kyselyladata.properties"
     * 
     * @throws ClassNotFoundException
     *             if the class for the relevant DB driver cannot be located
     * @throws SQLException
     *             if a database access error occurs
     */
    private void connect() throws ClassNotFoundException, SQLException {
	final String database = cf.getValue("database");
	final String username = cf.getValue("username");
	final String password = cf.getValue("password");

	// load the driver
	Class.forName("org.postgresql.Driver");

	// connect to the db
	db = DriverManager.getConnection("jdbc:postgresql:" + database,
		username, password);

	// get MetaData to confirm connection
	dbmd = db.getMetaData();

	Logger.print("Tietokanta:: Connection to "
		+ dbmd.getDatabaseProductName() + " "
		+ dbmd.getDatabaseProductVersion() + " successful.\n");
    }

}
